package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.repositories.CarTransferRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.CarTransferService;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by DF on 3/14/2017
 */
@Repository
public class CarTransferRepositoryImpl extends BaseRepository implements CarTransferRepository {

    @Override
    public PageData getTransferApprovalPagedList(CarTransferService.getTransferApprovalPagedList request) {
        String sql = "SELECT *  \n" +
                "FROM (  \n" +
                "	SELECT  \n" +
                "		a.id transId,  \n" +
                "		(select 车架号 from lsh库存表 where 库存编号 = a.stock_code) 'vin',  \n" +
                "		(select 车型详细 from lsh库存表 t1 join lsh车型 t2 on t1.车型编号 = t2.车型编号 where 库存编号 = a.stock_code) as 'modelDetail'  \n" +
                "	FROM car_transfer_log a  \n" +
                ") aa  \n" +
                "JOIN (  \n" +
                "	SELECT  \n" +
                "	   a.id transactionId,  \n" +
                "		quotedPriceCodeOrVIN,  \n" +
                "		approvalStatus,  \n" +
                "	   case approvalStatus when '0' then '待批复' when '1' then '已批复' when '2' then '驳回' else '' end approvalStatus_cn,  \n" +
                "		a.transcation_code,  \n" +
                "		case when type='8' THEN '调入' when type='9' THEN '调出' ELSE '' END AS 'type',  \n" +
                "		case when type='8' then '调车调入' when type='9' then '调车调出' else '' end as 'transferStatus',  \n" +
                "		REPLACE(SUBSTR(a.transcation_code,-19), '/', '-') AS 'transcation_code_time',  \n" +
                "		left(REPLACE(SUBSTR(a.transcation_code,-19), '/', '-'), 10) as 'date'  \n" +
                "	FROM pushApprovalWorkFlow  a  \n" +
                "	JOIN (  \n" +
                "		SELECT transcation_code, current_id  \n" +
                "		FROM (  \n" +
                "			SELECT  \n" +
                "				transcation_code, current_id,  \n" +
                // 在当前审批人之前的审批历史中查找审批状态为未处理的记录
                "				LOCATE('0', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS todoFlag,  \n" +
                "				-- 在当前审批人之前的审批历史中查找审批状态为驳回的记录  \n" +
                "				LOCATE('2', SUBSTR(GROUP_CONCAT(approvalStatus ORDER BY bb.step), 1, LENGTH(GROUP_CONCAT(approvalStatus ORDER BY bb.step)) - 1)) AS reject  \n" +
                "			FROM (  \n" +
                "				SELECT id as current_id, step as current_step, transcation_code as transca_code  \n" +
                "				FROM pushApprovalWorkFlow  \n" +
                "				WHERE TYPE IN (8, 9) AND userName= :userName  \n" +
                "			) aa  \n" +
                "			JOIN pushapprovalworkflow bb ON aa.transca_code = bb.transcation_code  \n" +
                "			-- 过滤当前step之后的审批流程  \n" +
                "			WHERE aa.current_step >= bb.step  \n" +
                "			GROUP BY transcation_code, aa.current_id  \n" +
                "		) a  \n" +
                "		-- 只显示之前已经全部处理完，且没有驳回状态的记录  \n" +
                "		WHERE todoFlag = 0 AND reject = 0  \n" +
                "	) b on a.id = b.current_id  \n" +
                "	WHERE userName = :userName  \n" +
                ") bb ON aa.transId = bb.quotedPriceCodeOrVIN  \n" +
                "ORDER BY transcation_code_time desc  \n";
        Map<String, String> params = new HashMap<>();
        params.put("userName", request.userName);
        return getPage(sql, params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public List getTransferInDetail(Integer transferId) {
        String sql = "select  \n" +
                "	if(sc <> '', sc, (select 申请销售顾问 from lsh库存表 where 库存编号 = a.stock_code)) as 'sc',  \n" +
                "	custom_name,  \n" +
                "   if(custom_name = '', 0, 1) 'existCustom',  \n" +
                "	new_car_sale_status,  \n" +
                "	reason,  \n" +
                "	expectedCustomPurchasing,  \n" +
                "	(select 生产号 from lsh库存表 where 库存编号 = a.compare_stock_code) 'compareProdCode',  \n" +
                "	(select 车架号 from lsh库存表 where 库存编号 = a.compare_stock_code) 'compareVin',  \n" +
                "	(select 车辆物流状态 from lsh库存表 where 库存编号 = a.compare_stock_code) 'compareLogisticsStatus',  \n" +
                "	(select 预计到店日期 from lsh库存表 where 库存编号 = a.compare_stock_code) 'compareExpectedInStock',  \n" +
                "	out_dealer,jxs.经销商名 AS DealerName, \n" +
                "	(select 车型详细 from lsh库存表 t1 join lsh车型 t2 on t1.车型编号 = t2.车型编号 where 库存编号 = a.stock_code) as 'modelDetail',  \n" +
                "	(select 生产号 from lsh库存表 where 库存编号 = a.stock_code) 'prodCode',  \n" +
                "	(select 车架号 from lsh库存表 where 库存编号 = a.stock_code) 'vin',  \n" +
                "	(select 采购价格未税 from lsh库存表 where 库存编号 = a.stock_code) 'purchasingPriceTaxe',  \n" +
                "	(select 实际MSRP含税 from lsh库存表 where 库存编号 = a.stock_code) 'actualMsrpTaxi',  \n" +
                "	(select 库龄天数 from lsh库存表 where 库存编号 = a.stock_code) 'stockDays',  \n" +
                "	arranged_money,  \n" +
                "	expectedLateInStockDate  \n" +
                "from car_transfer_log a  \n" +
                " LEFT JOIN  lsh经销商表 jxs ON a.`out_dealer`=jxs.`许可_BMBS`  \n" +
                "where a.id = :transferId  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("transferId", transferId);
        return getList(sql, params);
    }

    @Override
    public List getTransferOutDetail(Integer transferId) {
        String sql = "select  \n" +
                // 这里字段取错了，应该取调入经销商，但返回字段已经约定好了
                "	in_dealer out_dealer, jxs.经销商名 AS DealerName, \n" +
                "	(select 车型详细 from lsh库存表 t1 join lsh车型 t2 on t1.车型编号 = t2.车型编号 where 库存编号 = a.stock_code) as 'modelDetail',  \n" +
                "	(select 生产号 from lsh库存表 where 库存编号 = a.stock_code) 'prodCode',  \n" +
                "	(select 车架号 from lsh库存表 where 库存编号 = a.stock_code) 'vin',  \n" +
                "	(select 采购价格未税 from lsh库存表 where 库存编号 = a.stock_code) 'purchasingPriceTaxe',  \n" +
                "	(select 实际MSRP含税 from lsh库存表 where 库存编号 = a.stock_code) 'actualMsrpTaxi',  \n" +
                "	(select 库龄天数 from lsh库存表 where 库存编号 = a.stock_code) 'stockDays',  \n" +
                "	arranged_money,  \n" +
                "	expectedLateInStockDate,  \n" +
                "	expectedLateMoneyInAccount  \n" +
                "from car_transfer_log a  LEFT JOIN  lsh经销商表 jxs ON a.`in_dealer`=jxs.`许可_BMBS`  \n" +
                "where a.id = :transferId  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("transferId", transferId);
        return getList(sql, params);
    }

    @Override
    public List getTransferApprovalHistory(String transactionCode) {
        String sql = "select  \n" +
                "	id,  \n" +
                "	step,  \n" +
                "	permit,  \n" +
                "	type,  \n" +
                "	userName,  \n" +
                "	(select 人员姓名 from 用户 where 用户名 = userName) as userNameCn,  \n" +
                "	approvalStatus,  \n" +
                "	case approvalStatus  \n" +
                "		when '0' then '待批复'  \n" +
                "		when '1' then '通过'  \n" +
                "		when '2' then '驳回'  \n" +
                "	end as approvalStatusCn,  \n" +
                "	remark,  \n" +
                "	processTime  \n" +
                "from pushapprovalworkflow  \n" +
                "where transcation_code = :transactionCode  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("transactionCode", transactionCode);
        return getList(sql, params);
    }
}
